package com.roc.service.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.roc.newPage.IPageDao;
import com.roc.page.WhereHQLFieldBean;
import com.roc.pojo.TransSum;
import com.roc.service.ServiceException;
import com.roc.service.YsbTransSumService;
import com.roc.util.Constance;
import com.roc.util.constant.Role;
import com.roc.web.action.estrn.YsbTransSumSearchCondition;

@Service
public class YsbTransSumServiceImpl implements YsbTransSumService{
	
	private Logger logger=LogManager.getLogger(YsbTransSumServiceImpl.class);
	@Autowired
	private IPageDao pageDao;
	@Override
	public List<TransSum> queryTransSumList(YsbTransSumSearchCondition searchCondition, int startRow,
			int maxRow) throws ServiceException {
		List<Object> params = new ArrayList<Object>();
		List<TransSum> transSumList = new ArrayList<TransSum>();
		StringBuffer sb = new StringBuffer();
		int flag;
		if (searchCondition.getRoleType().equals(Role.AGENT.getCode() )||searchCondition.getRoleType().equals(Role.SFB_AGENT.getCode() )||searchCondition.getRoleType().equals(Role.O_MANAGER.getCode())) {
			sb = sb.append(" select tm.contact_phoneno,t.mcht_cd,t.sumTransAt, ")
					.append(" case when  t.sumTransAt >= 600000 then '已满' when  t.sumTransAt < 600000 then '未满' end, ")
					.append(" (select d.real_name from t_operators d where d.id = (select max(c.agent_id) from t_pos_agent c, t_pos_info e ")
					.append(" where c.serial_num = e.serial_num and c.pos_brand = e.pos_brand and e.serial_num = t.serial_num ")
					.append(" and e.merc_code = t.mcht_cd)),t.serial_num,(case b.asset_flag when '0' then '公司直采' when '1' then '公司代采' when '2' then '携机入网' end) ") 
					.append(" from t_pos_agent a,tbl_mcht_inf tm,(select tts.mcht_cd,tts.serial_num,sum(tts.trans_at/100) sumTransAt ") 
					.append(" from tbl_trans_sum tts where 1=1 ");
			flag=0;
		}else if(searchCondition.getRoleType().equals(Role.MARKET_DIRECT.getCode()) || searchCondition.getRoleType().equals(Role.MARKET_LEAD.getCode())){//销售人员
			sb = sb.append("select tm.contact_phoneno,t.mcht_cd,sum(t.trans_at/100), ")
					.append("case when sum(t.trans_at/100)>=600000 then '已满' when sum(t.trans_at/100)<600000 then '未满' end ")
					.append(",(select d.real_name from t_operators d where d.id=(select max(c.agent_id) from t_pos_agent c,t_pos_info e where c.serial_num=e.serial_num and c.pos_brand=e.pos_brand and e.serial_num=t.serial_num and e.merc_code=t.mcht_cd)),t.serial_num,(case b.asset_flag when '0' then '公司直采' when '1' then '公司代采' when '2' then '携机入网' end) ")
					.append(" from tbl_trans_sum t left join tbl_mcht_inf tm on t.mcht_cd=tm.mcht_cd left join t_pos_info b on t.serial_num=b.serial_num ")
					.append(" where tm.group_cd='3' and  exists (select 1 from t_oper_role tor where tm.uc_bc_cd_area = tor.agentid and tor.operid ="+searchCondition.getMarketorId()+") ");
			flag=1;
		}else{
			sb = sb.append("select tm.contact_phoneno,t.mcht_cd,sum(t.trans_at/100), ")
					.append("case when sum(t.trans_at/100)>=600000 then '已满' when sum(t.trans_at/100)<600000 then '未满' end ")
					.append(",(select d.real_name from t_operators d where d.id=(select max(c.agent_id) from t_pos_agent c,t_pos_info e where c.serial_num=e.serial_num and c.pos_brand=e.pos_brand and e.serial_num=t.serial_num and e.merc_code=t.mcht_cd)),t.serial_num,(case b.asset_flag when '0' then '公司直采' when '1' then '公司代采' when '2' then '携机入网' end) ")
					.append(" from tbl_trans_sum t left join tbl_mcht_inf tm on t.mcht_cd=tm.mcht_cd left join t_pos_info b on t.serial_num=b.serial_num ")
					.append(" where tm.group_cd='3' ");
			flag=1;
		}
		try {
			
			if(flag == 0){
				//商户号
				if (Constance.isNotEmpty(searchCondition.getMerNo())) {
//					filedBeanList.add(new WhereHQLFieldBean("t.mcht_cd", merNo.trim(), "=", false));
					sb.append(" and tts.mcht_cd=? ");
					params.add(searchCondition.getMerNo().trim());
					
				}
				
				if(Constance.isNotEmpty(searchCondition.getSn())){
//					filedBeanList.add(new WhereHQLFieldBean("t.pmt_no", sn.trim(), "=", false));
					sb.append(" and tts.serial_num=? ");
					params.add(searchCondition.getSn().trim());
				}
				
				if(Constance.isNotEmpty(searchCondition.getMinDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
		         	sb.append(" and tts.dt_create >= to_date(?, 'yyyymmdd') ");
		         	params.add(searchCondition.getMinDate().trim());
		       	}
				
				if(Constance.isNotEmpty(searchCondition.getMaxDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
					sb.append(" and tts.dt_create < to_date(?, 'yyyymmdd' )+1 ");
					params.add(searchCondition.getMaxDate().trim());
		       	}
				
				sb.append(" group by tts.mcht_cd,tts.serial_num "); 
				
				
				if(Constance.isNotEmpty(searchCondition.getIsFull())){
					if("01".equals(searchCondition.getIsFull())){//已满
						sb.append(" having sum(tts.trans_at/100)>=600000 ");
					}else{//未满
						sb.append(" having sum(tts.trans_at/100)<600000 ");
					}
				}
				
				sb.append(" ) t,t_pos_info b ")
					.append(" where t.serial_num=a.serial_num and t.mcht_cd=tm.mcht_cd and a.serial_num=b.serial_num and ")
					.append(" a.pos_brand=b.pos_brand and tm.group_cd='3' and a.agent_id=").append(searchCondition.getAgentId());
				
				if(Constance.isNotEmpty(searchCondition.getPhNo())){
					sb.append("and tm.contact_phoneno=? ");
					params.add(searchCondition.getPhNo().trim());
				}
				
				sb.append(" order by t.mcht_cd ");
			}else{
				if(Constance.isNotEmpty(searchCondition.getPhNo())){
					sb.append(" and tm.contact_phoneno=? ");
					params.add(searchCondition.getPhNo().trim());
				}
				
				//商户号
				if (Constance.isNotEmpty(searchCondition.getMerNo())) {
//					filedBeanList.add(new WhereHQLFieldBean("t.mcht_cd", merNo.trim(), "=", false));
					sb.append(" and t.mcht_cd=? ");
					params.add(searchCondition.getMerNo().trim());
					
				}
				
				
				if(Constance.isNotEmpty(searchCondition.getSn())){
//					filedBeanList.add(new WhereHQLFieldBean("t.pmt_no", sn.trim(), "=", false));
					sb.append(" and t.serial_num=? ");
					params.add(searchCondition.getSn().trim());
				}
				
				if(Constance.isNotEmpty(searchCondition.getOpId())){//操作员 
					sb.append(" and tm.rec_upd_opr=? ");
					params.add(searchCondition.getOpId());
			    }
				
				if(Constance.isNotEmpty(searchCondition.getUcbccdArea())){//机构管理员
					sb.append(" and tm.uc_bc_cd_area=? ");
					params.add(searchCondition.getUcbccdArea());
				}
				
				if(Constance.isNotEmpty(searchCondition.getMinDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
		         	sb.append(" and t.dt_create >= to_date(?, 'yyyymmdd') ");
		         	params.add(searchCondition.getMinDate().trim());
		       	}
				
				if(Constance.isNotEmpty(searchCondition.getMaxDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
					sb.append(" and t.dt_create < to_date(?, 'yyyymmdd' )+1 ");
					params.add(searchCondition.getMaxDate().trim());
		       	}
				
				sb.append("group by t.mcht_cd,tm.name_busi,t.serial_num,b.asset_flag,tm.contact_phoneno ");
				
				if(Constance.isNotEmpty(searchCondition.getIsFull())){
					if("01".equals(searchCondition.getIsFull())){//已满
						sb.append(" having sum(t.trans_at/100)>=600000 ");
					}else{//未满
						sb.append(" having sum(t.trans_at/100)<600000 ");
					}
				}
				
				sb.append(" order by t.mcht_cd ");
			}
			
//			logger.info("云商宝商户D0交易信息统计查询sql："+sb.toString());
			
			List<Object> searchRsList=null;
			
			
			
			searchRsList = pageDao.findAllByParams(sb.toString(), params, startRow, maxRow);
			
			transSumList = dealTheListSpecially(transSumList,searchRsList);
			
		} catch (Exception e) {
			logger.error("云商宝D0交易信息汇总查询List异常",e);
			throw new ServiceException("云商宝D0交易信息汇总查询List异常");
		}
		return transSumList;
	}
	
	private List<TransSum> dealTheListSpecially(List<TransSum> transSumList,
			List<Object> searchRsList) {
		if(searchRsList != null && searchRsList.size()>0){
			Iterator it = searchRsList.iterator();
			while(it.hasNext()){
				Object[] objArray = (Object[]) it.next();
				TransSum ts = new TransSum();
				ts.setRemark1(Constance.Null2EmptyTrim(objArray[0]));
				ts.setMchtCd(Constance.Null2EmptyTrim(objArray[1]));
				ts.setTransAt(Constance.Null2EmptyTrim(objArray[2]));
				ts.setRemark2(Constance.Null2EmptyTrim(objArray[3]));
				ts.setRemark3(Constance.Null2EmptyTrim(objArray[4]));
				ts.setSerialNum(Constance.Null2EmptyTrim(objArray[5]));
				ts.setRemark4(Constance.Null2EmptyTrim(objArray[6]));
				transSumList.add(ts);
			}
		}
		return transSumList;
	}
	
	@Override
	public int queryTransSumCount(YsbTransSumSearchCondition searchCondition) throws ServiceException {
		List<Object> params = new ArrayList<Object>();
		StringBuffer sb = new StringBuffer();
		int flag;
		if (searchCondition.getRoleType().equals(Role.AGENT.getCode() )||searchCondition.getRoleType().equals(Role.SFB_AGENT.getCode() )||searchCondition.getRoleType().equals(Role.O_MANAGER.getCode())) {
			sb = sb.append(" select count(1) ") 
					.append(" from t_pos_agent a,tbl_mcht_inf tm,(select tts.mcht_cd,tts.serial_num,sum(tts.trans_at/100) sumTransAt ") 
					.append(" from tbl_trans_sum tts where 1=1 "); 
			flag = 0;
		}else if(searchCondition.getRoleType().equals(Role.MARKET_DIRECT.getCode()) || searchCondition.getRoleType().equals(Role.MARKET_LEAD.getCode())){//销售人员
			sb = sb.append(" select count(1) from ( select tm.contact_phoneno,t.mcht_cd,sum(t.trans_at/100), ")
					.append("case when sum(t.trans_at/100)>=600000 then '已满' when sum(t.trans_at/100)<600000 then '未满' end ")
					.append(",(select d.real_name from t_operators d where d.id=(select max(c.agent_id) from t_pos_agent c,t_pos_info e where c.serial_num=e.serial_num and c.pos_brand=e.pos_brand and e.serial_num=t.serial_num and e.merc_code=t.mcht_cd)),t.serial_num,(case b.asset_flag when '0' then '公司直采' when '1' then '公司代采' when '2' then '携机入网' end) ")
					.append(" from tbl_trans_sum t left join tbl_mcht_inf tm on t.mcht_cd=tm.mcht_cd left join t_pos_info b on t.serial_num=b.serial_num ")
					.append(" where tm.group_cd='3' and  exists (select 1 from t_oper_role tor where tm.uc_bc_cd_area = tor.agentid and tor.operid ="+searchCondition.getMarketorId()+") ");
			flag=1;
		}else{
			sb = sb.append("select count(1) from ( select tm.contact_phoneno,t.mcht_cd,sum(t.trans_at/100), ")
					.append("case when sum(t.trans_at/100)>=600000 then '已满' when sum(t.trans_at/100)<600000 then '未满' end ")
					.append(",(select d.real_name from t_operators d where d.id=(select max(c.agent_id) from t_pos_agent c,t_pos_info e where c.serial_num=e.serial_num and c.pos_brand=e.pos_brand and e.serial_num=t.serial_num and e.merc_code=t.mcht_cd)),t.serial_num,(case b.asset_flag when '0' then '公司直采' when '1' then '公司代采' when '2' then '携机入网' end) ")
					.append(" from tbl_trans_sum t left join tbl_mcht_inf tm on t.mcht_cd=tm.mcht_cd left join t_pos_info b on t.serial_num=b.serial_num ")
					.append(" where tm.group_cd='3' ");
			flag=1;
		}
		int rowCount = 0;
		try {
			
			if(flag==0){
				
				
				//商户号
				if (Constance.isNotEmpty(searchCondition.getMerNo())) {
//					filedBeanList.add(new WhereHQLFieldBean("t.mcht_cd", merNo.trim(), "=", false));
					sb.append(" and tts.mcht_cd=? ");
					params.add(searchCondition.getMerNo().trim());
					
				}
				
				if(Constance.isNotEmpty(searchCondition.getSn())){
//					filedBeanList.add(new WhereHQLFieldBean("t.pmt_no", sn.trim(), "=", false));
					sb.append(" and tts.serial_num=? ");
					params.add(searchCondition.getSn().trim());
				}
				
				if(Constance.isNotEmpty(searchCondition.getMinDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
		         	sb.append(" and tts.dt_create >= to_date(?, 'yyyymmdd') ");
		         	params.add(searchCondition.getMinDate().trim());
		       	}
				
				if(Constance.isNotEmpty(searchCondition.getMaxDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
					sb.append(" and tts.dt_create < to_date(?, 'yyyymmdd' )+1 ");
					params.add(searchCondition.getMaxDate().trim());
		       	}
				
				sb.append(" group by tts.mcht_cd,tts.serial_num "); 
				
				
				if(Constance.isNotEmpty(searchCondition.getIsFull())){
					if("01".equals(searchCondition.getIsFull())){//已满
						sb.append(" having sum(tts.trans_at/100)>=600000 ");
					}else{//未满
						sb.append(" having sum(tts.trans_at/100)<600000 ");
					}
				}
				
				sb.append(" ) t,t_pos_info b ")
					.append(" where t.serial_num=a.serial_num and t.mcht_cd=tm.mcht_cd and a.serial_num=b.serial_num and ")
					.append(" a.pos_brand=b.pos_brand and tm.group_cd='3' and a.agent_id=").append(searchCondition.getAgentId());
				
				if(Constance.isNotEmpty(searchCondition.getPhNo())){
					sb.append("and tm.contact_phoneno=? ");
					params.add(searchCondition.getPhNo().trim());
				}
				
			}else{
				if(Constance.isNotEmpty(searchCondition.getPhNo())){
					sb.append("and tm.contact_phoneno=? ");
					params.add(searchCondition.getPhNo().trim());
				}
				
				//商户号
				if (Constance.isNotEmpty(searchCondition.getMerNo())) {
//					filedBeanList.add(new WhereHQLFieldBean("t.mcht_cd", merNo.trim(), "=", false));
					sb.append(" and t.mcht_cd=? ");
					params.add(searchCondition.getMerNo().trim());
					
				}
				
				if(Constance.isNotEmpty(searchCondition.getSn())){
//					filedBeanList.add(new WhereHQLFieldBean("t.pmt_no", sn.trim(), "=", false));
					sb.append(" and t.serial_num=? ");
					params.add(searchCondition.getSn().trim());
				}
				
				if(Constance.isNotEmpty(searchCondition.getOpId())){//操作员 
					sb.append(" and tm.rec_upd_opr=? ");
					params.add(searchCondition.getOpId());
			    }
				
				if(Constance.isNotEmpty(searchCondition.getUcbccdArea())){//机构管理员
					sb.append(" and tm.uc_bc_cd_area=? ");
					params.add(searchCondition.getUcbccdArea());
				}
				
				if(Constance.isNotEmpty(searchCondition.getMinDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
		         	sb.append(" and t.dt_create >= to_date(?, 'yyyymmdd') ");
		         	params.add(searchCondition.getMinDate().trim());
		       	}
				
				if(Constance.isNotEmpty(searchCondition.getMaxDate())){
//					filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
					sb.append(" and t.dt_create < to_date(?, 'yyyymmdd' )+1 ");
					params.add(searchCondition.getMaxDate().trim());
		       	}
				
				sb.append(" group by t.mcht_cd,tm.name_busi,t.serial_num,b.asset_flag,tm.contact_phoneno ");
				
				if(Constance.isNotEmpty(searchCondition.getIsFull())){
					if("01".equals(searchCondition.getIsFull())){//已满
						sb.append(" having sum(t.trans_at/100)>=600000 ");
					}else{//未满
						sb.append(" having sum(t.trans_at/100)<600000 ");
					}
				}
				
				sb.append(" ) ");
			}
			
			
//			logger.info("云商宝商户D0交易信息统计查询语句："+sb.toString());
			
			rowCount = pageDao.countByParams(sb.toString(),params);
			logger.info("云商宝商户D0交易信息统计数量查询sql结果："+rowCount);
			
		} catch (Exception e) {
			logger.error("云商宝D0交易信息汇总查询总条数异常",e);
			throw new ServiceException("云商宝D0交易信息汇总查询总条数异常");
		}
		return rowCount;
	}
	
	
	
}
